package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.common.DateFormatUtils;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.repositories.SalesClueSetRepository;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2017/6/30.
 */
@Repository
public class SalesClueSetRepositoryImpl extends BaseRepository implements SalesClueSetRepository {

    @Override
    public List querySalesClueNum(String permit, String scCode) {
        String sql = querySalesClueSetInfoSQL(scCode);
        Map param = new HashMap();
        param.put("permit", permit);
        String nowDate = null;
        try {
            nowDate = DateFormatUtils.getFormatDateToString(new Date(), "yyyy-MM-dd");
        } catch (Exception e) {
            e.printStackTrace();
        }
        param.put("nowDate", nowDate);
        if (!StringUtils.isNullOrBlank(scCode)) {
            param.put("scCode", scCode);
        }
        return this.getList(sql, param);
    }

    @Override
    public List queryExceedingTheTimitOgSales(String permit, String clueSuperiorLimit) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT * FROM ( ");
        sb.append(querySalesClueSetInfoSQL(null));
        sb.append(" ) a WHERE 1 = 1 ");
        if (!StringUtils.isNullOrBlank(clueSuperiorLimit)) {
            sb.append(" AND a.COUNTS - :clueSuperiorLimit >= 0 ");
            param.put("clueSuperiorLimit", clueSuperiorLimit);
        };
        param.put("permit", permit);
        String nowDate = null;
        try {
            nowDate = DateFormatUtils.getFormatDateToString(new Date(), "yyyy-MM-dd");
        } catch (Exception e) {
            e.printStackTrace();
        }
        param.put("nowDate", nowDate);
        return this.getList(sb.toString(), param);
    }

    @Override
    public List queryFailToReachTheTeilingOfSales(String permit, String clueSuperiorLimit, String scCode) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT * FROM ( ");
        sb.append(querySalesClueSetInfoSQL(null));
        sb.append(" ) a WHERE 1 = 1 ");
        if (!StringUtils.isNullOrBlank(scCode)) {
            sb.append(" AND a.scCode <> :scCode ");
            param.put("scCode", scCode);
        }
        if (!StringUtils.isNullOrBlank(clueSuperiorLimit)) {
            sb.append(" AND (:clueSuperiorLimit - a.COUNTS > 0) ");
            param.put("clueSuperiorLimit", clueSuperiorLimit);
        };
        param.put("permit", permit);
        String nowDate = null;
        try {
            nowDate = DateFormatUtils.getFormatDateToString(new Date(), "yyyy-MM-dd");
        } catch (Exception e) {
            e.printStackTrace();
        }
        param.put("nowDate", nowDate);
        return this.getList(sb.toString(), param);
    }

    @Override
    public List queryBelowTheLimitOfSales(String permit, String clueUnderLimit) {
        StringBuffer sb = new StringBuffer();
        Map param = new HashMap();
        sb.append(" SELECT * FROM ( ");
        sb.append(querySalesClueSetInfoSQL(null));
        sb.append(" ) a WHERE 1 = 1 ");
        if (!StringUtils.isNullOrBlank(clueUnderLimit)) {
            sb.append(" AND a.COUNTS - :clueUnderLimit < 0 ");
            param.put("clueUnderLimit", clueUnderLimit);
        };
        param.put("permit", permit);
        String nowDate = null;
        try {
            nowDate = DateFormatUtils.getFormatDateToString(new Date(), "yyyy-MM-dd");
        } catch (Exception e) {
            e.printStackTrace();
        }
        param.put("nowDate", nowDate);
        return this.getList(sb.toString(), param);
    }

    private String querySalesClueSetInfoSQL (String scCode) {
        StringBuffer sql = new StringBuffer();
        sql.append( " SELECT " +
                " 	a.`用户名` scCode,	a.`人员姓名` scName,a.品牌 brand, a.`许可` permit,IFNULL(b.counts,0) counts " +
                " FROM `用户` a  " +
                " LEFT JOIN ( " +
                " 	SELECT  " +
                " 	a.所属销售顾问 scCode, COUNT(a.所属销售顾问) counts  " +
                " 	FROM (  " +
                " 		SELECT  " +
                " 			m.`许可`,m.`所属销售顾问`  " +
                " 		FROM 顾客记录汇总 m  " +
                " 		WHERE m.许可 = :permit AND (m.顾客状态 <> '战败' OR m.`顾客状态` IS NULL) AND NOT EXISTS (  " +
                " 			SELECT  " +
                " 				a.`顾客编号`  " +
                " 			FROM `报价单表` a  " +
                " 				JOIN	vw_max_quoted_code b ON a.`报价编号`=b.`报价编号`  " +
                " 				JOIN	`报价订单表` c ON a.`订单编号` = c.`订单编号`  " +
                " 				WHERE c.`订单审批通过日期` IS NOT NULL  " +
                " 				AND c.`订单失销日期` IS NULL  " +
                " 				AND a.顾客编号 = m.顾客编号  " +
                " 		)  " +
                " 		UNION ALL  " +
                " 		SELECT  " +
                " 			m.`许可`,m.`所属销售顾问`  " +
                " 		FROM lsh导入名单 m  " +
                " 		WHERE m.许可 = :permit AND  m.`是否有效线索` = '是'  " +
                " 		AND m.`是否线索` = '是'  " +
                " 		AND m.`线索创建日期` >= date_sub(date_sub(date_format(:nowDate,'%y-%m-%d'),interval extract(day from :nowDate) -1 day),interval 2 month)  " +
                " 		AND NOT EXISTS (  " +
                " 			SELECT  " +
                " 				a.`电话号码`  " +
                " 			FROM 顾客记录汇总 a  " +
                " 			WHERE a.许可 = :permit  " +
                " 			AND a.电话号码 = m.电话号码  " +
                " 		)  " +
                " 	) a  " +
                " 	WHERE a.所属销售顾问 <> ''  " +
                " 	AND a.所属销售顾问 IS NOT NULL  " +
                " 	GROUP BY a.所属销售顾问 " +
                " ) b ON a.用户名 = b.scCode " +
                " WHERE a.许可 = :permit " +
                " and (a.角色 = '销售顾问' AND 接待状态 = '是') " +
                " AND a.删除状态 <> '已删除' " +
                " AND (a.销售顾问职位状态 <> '离职' OR a.销售顾问职位状态 IS NULL) ");
        if (!StringUtils.isNullOrBlank(scCode)) {
            sql.append(" AND a.用户名 = :scCode ");
        }
        return sql.toString();
    }
}
